package com.rocsea.robot.service;

import com.rocsea.robot.constant.EtlConstant;
import com.rocsea.robot.dao.DirectoryDAO;
import com.rocsea.robot.dao.StepAttributeDAO;
import com.rocsea.robot.dao.StepDAO;
import com.rocsea.robot.dao.TransformationDAO;
import com.rocsea.robot.exception.BusinessException;
import com.rocsea.robot.model.bo.TableCheckConfig;
import com.rocsea.robot.model.entity.*;
import com.rocsea.robot.model.enums.DeleteTypeEnum;
import com.rocsea.robot.model.enums.KettleBuildModuleEnum;
import com.rocsea.robot.model.enums.ServiceModuleEnum;
import com.rocsea.robot.model.enums.TableTypeEnum;
import com.rocsea.robot.model.request.BatchSwitchDatasourceRequest;
import com.rocsea.robot.model.request.EtlSyncCheckConfigBuildRequest;
import com.rocsea.robot.utils.MysqlExtBuilderUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * @Author RocSea
 * @Date 2022/12/2
 */
@Service
public class KettleExtService {
    @Value("${public.env}")
    private String env;
    @Resource
    private DirectoryDAO directoryDAO;
    @Resource
    private TransformationDAO transformationDAO;
    @Resource
    private StepDAO stepDAO;
    @Resource
    private StepAttributeDAO stepAttributeDAO;
    private static final String STEP_TABLE_INPUT = "table_input";
    private static final String STEP_QUERY_FROM_TABLENAME = "query_from_tablename";
    private static final String CODE_ID_CONNECTION = "id_connection";


    public String autoBuild(EtlSyncCheckConfigBuildRequest request) {
        List<EtlSyncCheckConfigDO> etlSyncCheckConfigDOs = listEtlSyncCheckConfigDO(request);
        StringBuilder sql = new StringBuilder();
        for (EtlSyncCheckConfigDO etlSyncCheckConfigDO : etlSyncCheckConfigDOs) {
            sql.append(MysqlExtBuilderUtils.buildEtlSyncCheckConfig(etlSyncCheckConfigDO));
        }
        return sql.toString();
    }

    private List<EtlSyncCheckConfigDO> listEtlSyncCheckConfigDO(EtlSyncCheckConfigBuildRequest request) {
        List<EtlSyncCheckConfigDO> result = new ArrayList<>();
        for (TableCheckConfig tableCheckConfig : request.getTableCheckConfigList()) {
            EtlSyncCheckConfigDO etlSyncCheckConfigDO = new EtlSyncCheckConfigDO();
            etlSyncCheckConfigDO.setSourceServiceModule(request.getSourceServiceModule());
            etlSyncCheckConfigDO.setSourceSchemaName(request.getSourceSchemaName());
            etlSyncCheckConfigDO.setSourceTableName(tableCheckConfig.getSourceTableName());
            etlSyncCheckConfigDO.setSourceDeleteType(tableCheckConfig.getSourceDeleteType());
            if (Objects.isNull(tableCheckConfig.getSourceWhereCondition())
                    && DeleteTypeEnum.PHYSICAL.getValue().equals(tableCheckConfig.getSourceDeleteType())) {
                etlSyncCheckConfigDO.setSourceWhereCondition(EtlConstant.DEFAULT_SOURCE_WHERE_CONDITION);
            } else {
                etlSyncCheckConfigDO.setSourceWhereCondition(tableCheckConfig.getSourceWhereCondition());
            }
            etlSyncCheckConfigDO.setSourceTableType(TableTypeEnum.SINGLE_TABLE.getValue());
            etlSyncCheckConfigDO.setTargetServiceModule(ServiceModuleEnum.SELL_OUT.getValue());
            etlSyncCheckConfigDO.setTargetSchemaName(Objects.isNull(request.getTargetSchemaName()) ? EtlConstant.DEFAULT_SELL_DATABASE :
                    request.getTargetSchemaName());
            etlSyncCheckConfigDO.setTargetTableName(Objects.isNull(tableCheckConfig.getTargetTableName()) ?
                    tableCheckConfig.getSourceTableName() : tableCheckConfig.getTargetTableName());
            etlSyncCheckConfigDO.setTargetDeleteType(DeleteTypeEnum.LOGIC.getValue());
            etlSyncCheckConfigDO.setTargetWhereCondition(tableCheckConfig.getTargetWhereCondition());
            etlSyncCheckConfigDO.setTargetTableType(TableTypeEnum.SINGLE_TABLE.getValue());
            result.add(etlSyncCheckConfigDO);
        }
        return result;
    }

    public String formatMss(String request) {
        int tableStartIndex = request.indexOf(".[");
        int tableEndIndex = request.indexOf("] (");
        String tableName = request.substring(tableStartIndex + 2, tableEndIndex);

        int splitIndex = request.indexOf("ALTER TABLE");
        String head = request.substring(0, splitIndex);
        String tail = request.substring(splitIndex);
        String headProcessResult = processHead(tableName, head);
        String tailProcessResult = processTail(tail);
        return headProcessResult + tailProcessResult;
    }

    private String processHead(String tableName, String head) {
        String[] headArr = head.split(",");
        StringBuffer firstHalfFormat = new StringBuffer();
        for (int i = 0; i < headArr.length; i++) {
            String s = headArr[i];
            if (!s.contains("update_time_id") && !s.contains("id")) {
                s = s.replace("NOT", "");
            }
            if (s.contains("CONSTRAINT")) {
                int start = s.indexOf("[");
                int end = s.indexOf("]");
                String pkName = s.substring(start + 1, end);
                s = s.replace(pkName, "PK__" + tableName);

            }
            if (s.contains("nvarchar")) {
                int start = s.indexOf("nvarchar(");
                int end = s.indexOf(")");
                String length = s.substring(start + 9, end);
                if (!"max".equals(length)) {
                    int len = 2 * Integer.valueOf(length);
                    String realLength = len > 4000 ? "max" : String.valueOf(len);
                    s = s.replace(length, realLength);
                }
            }
            s = s.replace("IGNORE_DUP_KEY = OFF", "IGNORE_DUP_KEY = ON");
            s = s.replace("tinyint", "int");
            s = s.replace("smallint", "int");
            s = s.replace("COLLATE Chinese_PRC_CI_AS", "");
            s = s.replace("datetime2(7)", "datetime2(0)");
            firstHalfFormat.append(s);
            if (i < headArr.length - 1) {
                firstHalfFormat.append(",");
            }
        }
        return firstHalfFormat.toString();
    }

    private String processTail(String tail) {
        int start = tail.indexOf("CREATE NONCLUSTERED INDEX");
        int end = tail.indexOf("EXEC");
        if (-1 != start) {
            String replaceOld = tail.substring(start, end);
            tail = tail.replace(replaceOld, "");
        }
        return tail;
    }

    public String switchDatasource(BatchSwitchDatasourceRequest request) {
        if (!"qa".equals(env)) {
            throw new BusinessException("请检查环境");
        }
        KettleBuildModuleEnum.geText(request.getModuleType());
        Optional<DirectoryDO> parent = directoryDAO.getOne(request.getModuleType(), request.getSchemaName());
        if (!parent.isPresent()) {
            throw new BusinessException("父目录不存在");
        }
        List<DirectoryDO> directoryDOs = directoryDAO.getList(parent.get().getIdDirectory().intValue(), request.getTableNameList());
        if (directoryDOs.size() != request.getTableNameList().size()) {
            throw new BusinessException("检查表名是否都正确");
        }
        StringBuilder result = new StringBuilder();
        List<StepAttributeDO> stepAttributeDOList = new ArrayList<>();
        for (DirectoryDO directoryDO : directoryDOs) {
            List<TransformationDO> transformationDOs = transformationDAO.queryByIdDirectory(directoryDO.getIdDirectory());
            for (TransformationDO transformationDO : transformationDOs) {
                List<StepDO> stepDOs = stepDAO.getList(transformationDO.getIdTransformation(), Arrays.asList(STEP_TABLE_INPUT, STEP_QUERY_FROM_TABLENAME));
                for (StepDO stepDO : stepDOs) {
                    Optional<StepAttributeDO> stepAttributeDO = stepAttributeDAO.getOne(stepDO.getIdStep(), CODE_ID_CONNECTION);
                    if (stepAttributeDO.isPresent()) {
                        stepAttributeDOList.add(stepAttributeDO.get());
                    }
                }
            }
        }
        for (StepAttributeDO stepAttributeDO : stepAttributeDOList) {
            result.append(MysqlExtBuilderUtils.buildSwitchDatasource(stepAttributeDO, request.getNewDatasourceId()));
        }
        return result.toString();
    }
}
